package com.wanxi.dao.Impl;

import com.wanxi.dao.LawyerDao;
import com.wanxi.model.LawyerModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Li
 * @Date 2023/5/6 14:58
 */
public class LawyerDaoImpl implements LawyerDao {
    /**
     * 新增数据
     * @param lawyerModel
     * @return
     */
    public int add(LawyerModel lawyerModel) {
        String sql = "INSERT INTO `lawyers` (`name`, `image`, `age`, `sex`, `role`, `phone`, `introduce`, `create_time`, `update_time`, `enable`, `remark`) " +
                "VALUES ('"+lawyerModel.getName()+"', '"+lawyerModel.getImage()+"', '"+lawyerModel.getAge()+"', " +
                "'"+lawyerModel.getSex()+"', '"+lawyerModel.getRole()+"', '"+lawyerModel.getPhone()+"', '"+lawyerModel.getIntroduce()+"', " +
                "now(),  sysdate(), '"+lawyerModel.getEnable()+"', '"+lawyerModel.getRemark()+"');";
        return JDBC.update(sql);
    }

    /**
     * 修改数
     * @param lawyerModel
     * @return
     */
    public int update(LawyerModel lawyerModel) {
        String sql = "UPDATE `lawyers` SET `name` = '"+lawyerModel.getName()+"', " +
                "`image` = '"+lawyerModel.getImage()+"', " +
                "`age` = '"+lawyerModel.getAge()+"'," +
                " `sex` = '"+lawyerModel.getSex()+"', " +
                "`role` = '"+lawyerModel.getRole()+"', " +
                "`phone` = '"+lawyerModel.getPhone()+"'," +
                " `introduce` = '"+lawyerModel.getIntroduce()+"'," +
                " `update_time` = now(), " +
                "`enable` = '"+lawyerModel.getEnable()+"', " +
                "`remark` = '"+lawyerModel.getRemark()+"' WHERE (`id` = '"+lawyerModel.getId()+"');";
        return JDBC.update(sql);
    }

    /**
     * 删除数据
     * @param lawyerModel
     * @return
     */
    public int delete(LawyerModel lawyerModel) {
        String sql = "delete from `lawyers` where id="+lawyerModel.getId();
        return JDBC.update(sql);
    }

    /**
     * 判断姓名是否相同
     * @param lawyerModel
     * @return
     */
    public boolean findSameName(LawyerModel lawyerModel) {
        String sql = "select * from lawyers where name='" + lawyerModel.getName() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }
//判断电话是否相同
    @Override
    public boolean findSamePhone(LawyerModel lawyerModel) {
        String sql = "select * from lawyers where phone='" + lawyerModel.getPhone() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    public List<LawyerModel> findAll(LawyerModel lawyerModel) {
        int start = (lawyerModel.getPage()-1)*lawyerModel.getLimit();
        String sql = "select * from lawyers where true";
        sql += getWhereSql(lawyerModel);
        sql += " order by id desc";
        sql += " limit "+start+","+lawyerModel.getLimit()+" ";
        ResultSet query = JDBC.query(sql);
        List<LawyerModel> list = new ArrayList<LawyerModel>();
        try{
            while (query.next()){
                list.add(LawyerModel.builder()
                        .id(query.getInt("id"))
                        .name(query.getString("name"))
                        .sex(query.getString("sex"))
                        .age(query.getInt("age"))
                        .image(query.getString("image"))
                        .role(query.getString("role"))
                        .phone(query.getString("phone"))
                        .introduce(query.getString("introduce"))
                        .enable(query.getInt("enable"))
                        .remark(query.getString("remark"))
                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

//    模糊查询方法
    private String getWhereSql(LawyerModel lawyerModel){
        String sql = "";
        if (!"".equals(lawyerModel.getName())){
            sql += " and name like '%"+lawyerModel.getName()+"%' ";
        }
        if (!"".equals(lawyerModel.getSex())){
            sql += " and sex ='"+lawyerModel.getSex()+"' ";
        }
        if (!"".equals(lawyerModel.getRole())){
            sql += " and role like '%"+lawyerModel.getRole()+"%' ";
        }
        if (!"".equals(lawyerModel.getPhone())){
            sql += " and phone like '%"+lawyerModel.getPhone()+"%' ";
        }
        if (lawyerModel.getEnable()!=-1) {
            sql += " and enable = '" + lawyerModel.getEnable() + "' ";
        }
        if (lawyerModel.getStartAge()!=-1){
            sql += " and age >= '"+lawyerModel.getStartAge()+"' ";
        }
        if (lawyerModel.getEndAge()!=-1){
            sql += " and age <= '"+lawyerModel.getEndAge()+"' ";
        }
        return sql;
    }
    public LawyerModel findById(LawyerModel lawyerModel) {
        String sql = " select * from lawyers where id = '"+lawyerModel.getId()+"' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()){
             return LawyerModel.builder()
                     .id(query.getInt("id"))
                     .name(query.getString("name"))
                     .image(query.getString("image"))
                     .age(query.getInt("age"))
                     .sex(query.getString("sex"))
                     .role(query.getString("role"))
                     .introduce(query.getString("introduce"))
                     .phone(query.getString("phone"))
                     .enable(query.getInt("enable"))
                     .remark(query.getString("remark"))
                     .build();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    public Integer count(LawyerModel lawyerModel) {
        String sql = "select count(*) as count from lawyers where true";
        sql += getWhereSql(lawyerModel);
   return JDBC.count(sql);
    }

    @Override
    public Integer totalCount() {
        String sql = "select count(*) as count from lawyers";
        return JDBC.count(sql);
    }
}
